<?php
require('adodb5/adodb-exceptions.inc.php');
require('adodb5/adodb.inc.php');

class Application_Model_Adodb{
    public function obrir_bd() {
        $hostname = 'localhost';
        $username = 'root';
        $password = '';
        $database = 'pract_uf3';
        try {
            $conn = ADONewConnection('mysql');
            $conn->PConnect($hostname, $username, $password, $database);
            return $conn;
        } catch (Exception $e) {
            echo "La conexio a fallat...";
        }
    }
    public function tancar_bd($link) {
        $link->close();
    }

    /*     * ******************************* DEPARTAMENTS *********************************** */

    public function getDept($id_dept) {
        $link = $this->obrir_bd();
        $sql = ("SELECT id,nom,descripcio FROM dept where id=?");
        $query = $link->prepare($sql);
        $rs = $link->Execute($query, array($id_dept));
        $dept=null;
        foreach ($rs as $row) {
            $dept = $row;
        }
        $rs->close();
        $this->tancar_bd($link);
        $this->tancar_bd($link);
        return $dept;
    }

    public function addDept($n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO dept (nom,descripcio) VALUES (?,?)";
        $query = $link->prepare($sql);
        $rs = $link->Execute($query, array($n_dept, $d_dept));
        $this->tancar_bd($link);
    }

    public function editDept($id_dept, $n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "UPDATE dept SET nom =?, descripcio =? WHERE id=?";
        $query = $link->prepare($sql);
        $rs = $link->Execute($query, array($n_dept, $d_dept, $id_dept));
        $this->tancar_bd($link);
    }

    public function deleteDept($id_dept) {
        $link = $this->obrir_bd();
        //Inici transacció
        $link->StartTrans(); 
        
        //Esborrar empleats d'aquell dept.
        $sql= "DELETE FROM emp WHERE id_dept=?";
        $query=$link->prepare($sql);
        $link->Execute($query,array($id_dept));
        
        //Esborrar dept
        $sql= "DELETE FROM dept WHERE id=?";
        $query=$link->prepare($sql);
        $link->Execute($query,array($id_dept));
  
        //Fi transacció. Si ha fallat algun delete farà un rollback. Sinó farà commit.
        $link->CompleteTrans();
        $this->tancar_bd($link);
    }

    public function listDepts() {
        $i = 0;
        $depts = array();
        $link = $this->obrir_bd();
        $rs = $link->Execute("select * from dept");
        foreach ($rs as $row) {
            $depts[$i] = $row;
            $i++;
        }
        $rs->close();
        $this->tancar_bd($link);
        return $depts;
    }

    public function listDeptsSelect() {
        $depts = array();
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom FROM dept";
        $rs = $link->Execute($sql);
        foreach ($rs as $row) {
            $depts[$row['id']] = $row['nom'];
        }
        $rs->close();
        $this->tancar_bd($link);
        return $depts;
    }

    /*     * ******************************* EMPS *********************************** */

    public function getEmp($id_emp) {
        $link = $this->obrir_bd();
        $sql = "SELECT nom,cognom,dni,id_dept as departament FROM emp where id=?";
        $query = $link->prepare($sql);
        $rs = $link->Execute($query, array($id_emp));
        foreach ($rs as $row) {
            $emp = $row;
        }
        $rs->close();
        $this->tancar_bd($link);
        return $emp;
    }

    public function addEmp($nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO emp (nom,cognom,dni,id_dept) VALUES (?,?,?,?)";
        $query = $link->prepare($sql);
        $rs = $link->Execute($query, array($nom_e, $cognom_e, $dni_e, $id_d));
        $this->tancar_bd($link);
    }

    public function editEmp($id_e, $nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "UPDATE emp SET nom =?, cognom =?,dni=?,id_dept=? WHERE id=?";
        $query = $link->prepare($sql);
        $rs = $link->Execute($query, array($nom_e, $cognom_e, $dni_e, $id_d, $id_e));
        $this->tancar_bd($link);
    }

    public function deleteEmp($id_e) {
        $link = $this->obrir_bd();
        $sql = "DELETE FROM emp WHERE id=?";
        $query = $link->prepare($sql);
        $rs = $link->Execute($query, array($id_e));
        $this->tancar_bd($link);
    }

    public function listEmps() {
        $i = 0;
        $emps = array();
        $link = $this->obrir_bd();
        $sql = "SELECT e.id,e.nom,e.cognom,e.dni,d.nom as departament FROM emp e, dept d WHERE e.id_dept=d.id";

        $rs = $link->Execute($sql);
        foreach ($rs as $row) {
            $emps[$i] = $row;
            $i++;
        }
        $rs->close();
        $this->tancar_bd($link);
        return $emps;
    }

}
